using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;

namespace SEO.DAL
{

    /// <summary>
    /// 版权所有: 版权所有(C) 2012，云里科技
    /// 内容摘要: EDMSQLITEContent
    /// 完成日期：2012年8月15日
    /// 版    本：V1.0 
    /// 作    者：Adin
    /// </summary>
    public class EDMSQLITEContent
    {
        /// <summary>
        /// 检测EmailContent表是否存在，如果不存在就创建
        /// </summary>
        /// <returns></returns>
        public static bool CheckEmailContentIsExist()
        {
            bool bValue = false;
            try
            {
                string strNum = "";
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "SELECT COUNT(*) as num FROM sqlite_master where type='table' and name='EmailContent'";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                if (dr.Read())
                {
                    strNum = dr["num"].ToString();
                    dr.Dispose();
                    cn.Close();

                    if (strNum == "0")
                    {
                        CreateTableForEmailContent();
                        bValue = false;
                    }
                    else
                    {
                        bValue = true;
                    }
                }
                else
                {
                    dr.Dispose();
                    cn.Close();
                }
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("检测EmailContent表是否存在 -> " + ex.Message);
            }

            return bValue;
        }

        /// <summary>
        /// 创建EmailContent表
        /// </summary>
        public static void CreateTableForEmailContent()
        {
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                //autoincrement
                string strSql = "create table EmailContent(content_id int primary key,mail_title nvarchar(200),mail_content ntext,mail_attachment nvarchar(50),user_id int,mail_status int,post_status char(1),server_id int)";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                cm.ExecuteNonQuery();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("创建EmailContent表 -> " + ex.Message);
            }
        }

        /// <summary>
        /// 新增邮件内容
        /// </summary>
        /// <param name="strTitle">邮件标题</param>
        /// <param name="strContent">邮件内容</param>
        /// <param name="strStatus">状态</param>
        public static void InsertContent(string strServerID,string strTitle,string strContent,string strStatus)
        {
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "insert into EmailContent(content_id,mail_title,mail_content,mail_status,server_id) " +
                    " values(" + GetAutoAddNum() + ",'" + strTitle + "','" + strContent + "'," + strStatus + "," + strServerID + ")";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                cm.ExecuteNonQuery();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("添加SMTP -> " + ex.Message);
            }
        }

        /// <summary>
        /// EmailContent获取最大ID
        /// </summary>
        /// <returns></returns>
        public static int GetAutoAddNum()
        {
            int iNum = 0;
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "select content_id from EmailContent order by content_id desc limit 1";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                if (dr.Read())
                {
                    iNum = int.Parse(dr["content_id"].ToString()) + 1;
                }
                else
                {
                    iNum = 1;
                }
                dr.Dispose();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("EmailContent获取最大ID -> " + ex.Message);
            }

            return iNum;
        }

        /// <summary>
        /// 更新邮件内容状态
        /// </summary>
        /// <param name="strStatus">1为已发布，0为未发布</param>
        /// <param name="iContentID">内容ID</param>
        public static void UpdateEmailContentStatus(string strStatus,int iContentID)
        {
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "update EmailContent set post_status=" + strStatus + " where content_id=" + iContentID;
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                cm.ExecuteNonQuery();
                cn.Close();

                if (CountAllContentAmount() == 1)//当用户只添加一条邮件内容时
                {
                    SEO.DAL.EDMSQLITEContent.UpdateAllEmailContentStatus("0");
                }

                if (GetCountContentAmount() == 1)//当用户添加多条邮件内容，发送到最后一条时
                {
                    SEO.DAL.EDMSQLITEContent.UpdateAllEmailContentStatus("0");
                }
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("更新邮件内容状态 -> " + ex.Message);
            }
        }

        /// <summary>
        /// 获取未发布邮件内容数量
        /// </summary>
        /// <returns></returns>
        public static int GetCountContentAmount()
        {
            int iNum = 0;
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "SELECT COUNT(content_id) AS cnum FROM EmailContent WHERE (post_status = 0)";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                if (dr.Read())
                {
                    iNum = int.Parse(dr["cnum"].ToString());
                }
                dr.Dispose();
                cn.Close();

            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("获取未发布邮件内容数量 -> " + ex.Message);
            }

            return iNum;
        }

        /// <summary>
        /// 更新所有EmailContent信息状态
        /// </summary>
        /// <param name="strStatus">1为已发布，0为未发布</param>
        public static void UpdateAllEmailContentStatus(string strStatus)
        {
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "update EmailContent set post_status=" + strStatus;
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                cm.ExecuteNonQuery();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("更新EmailContent状态 -> " + ex.Message);
            }
        }

        /// <summary>
        /// 删除EmailContent
        /// </summary>
        public static void DeleteAllEmailContent()
        {
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "delete from EmailContent";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                cm.ExecuteNonQuery();
                cn.Close();
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("删除EmailContent -> " + ex.Message);
            }
        }

        public static int CountAllContentAmount()
        {
            int iNum = 0;
            try
            {
                SQLiteConnection cn = new SQLiteConnection(SEO.DBSqlHelp.EDMDBHelper.strEDMEMailsConn);
                cn.SetPassword(SEO.Utility.CommonHelper.SQLITEPASSWORD);
                cn.Open();
                string strSql = "SELECT COUNT(content_id) AS cnum FROM EmailContent";
                SQLiteCommand cm = new SQLiteCommand(strSql, cn);
                SQLiteDataReader dr = cm.ExecuteReader();
                if (dr.Read())
                {
                    iNum = int.Parse(dr["cnum"].ToString());
                }
                dr.Dispose();
                cn.Close();

            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("获取未发布邮件内容数量 -> " + ex.Message);
            }

            return iNum;
        }



    }
}
